﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using QuanLyThuVien1.DTO;
using System.Data;
namespace QuanLyThuVien1.DAO
{
    public class SACH_DAO
    {
        
       public DataTable TimSach(string m_TenSach, string m_TheLoai, string m_Tacgia)
        {
            DataTable m_DT = new DataTable();
            OleDbConnection m_OleDb = KETNOI.taoKetNoi();
            string m_sSQL;
            m_sSQL = "Select SACH.tenSach,SACH.tacGia, LOAISACH.theLoai,DAUSACH.tenDauSach Where ";
            m_sSQL += " SACH.maDS = DAUSACH.maDauSach AND LOAISACH.maLoaiSach = SACH.ma_Loai AND ";
            if (m_TenSach != "")
                m_sSQL += "DAUSACH.tenDauSach like '%" + m_TenSach + "%' AND";
            
            if (m_TheLoai != "")
                m_sSQL += " LOAISACH.theLoai = '" + m_TheLoai + "' AND";
            if (m_Tacgia != "")
                m_sSQL += " SACH.tacGia like '%" + m_Tacgia + "%' AND";
            m_sSQL = m_sSQL.Substring(0, m_sSQL.Length - 4);
            OleDbCommand m_OleDbcmd = new OleDbCommand(m_sSQL, m_OleDb);
            OleDbDataAdapter m_OleDbAdap = new OleDbDataAdapter(m_OleDbcmd);

            m_OleDbAdap.Fill(m_DT);

            m_OleDb.Close();
            return m_DT;
       }

          public string timNXB(string tenNXB)
        {
            OleDbConnection connect = new OleDbConnection();
            DataTable tim = new DataTable();
            string connecstr = "select maNXB from NHAXUATBAN where tenNXB = ?";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.Parameters.Add("tenNXB", OleDbType.LongVarChar);
            command.Parameters["tenNXB"].Value = tenNXB;
            OleDbDataAdapter m_oleDbAtimMaNXB = new OleDbDataAdapter(command);
            m_oleDbAtimMaNXB.Fill(tim);
            connect.Close();
            string ma = tim.Rows[0][0].ToString();
            return ma;
        }
        public void themSach(SACH s)
        {

            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "insert into SACH values('"+s.MASACH+"','"+s.TENSACH+"','"+s.MANXB+"','"+s.MADAUSACH+"','"+s.MALOAI+"','"+s.NAMXUATBAN+"','"+s.DONGIA+"','"+s.NGAYNHAN+"','"+s.MANGUOINHAN+"','"+s.TENTACGIA+"','"+s.soCuon+"')";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
           
            command.ExecuteNonQuery();
            connect.Close();
        }
        public int soCuonSach(string maSach)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select soCuon FROM SACH  where maSach =  '"+maSach+"'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            OleDbDataReader Reader = command.ExecuteReader();
            int so = 0 ;
            while (Reader.Read())
            {
                so =  Reader.GetInt32(0);
            }
            connect.Close();
            return so;
        }
        public DataTable TimSachTheoMaSach(string tenSach)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select 1 as stt,s.maSach,s.tenSach,d.tenDauSach, l.theLoai,s.tacGia,n.tenNXB,s.donGia,s.soCuon,d.soTap from SACH as s,NHAXUATBAN as n,DAUSACH as d,LOAISACH as l where ";
            connecstr += "n.maNXB = s.ma_NXB and s.maDS = d.maDauSach and l.maLoaiSach = s.ma_Loai and s.tenSach = '"+tenSach+"'"; 
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public DataTable TimSachTheoLoaiSach(string loaiSach)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select 1 as stt,s.maSach,s.tenSach,d.tenDauSach, l.theLoai,s.tacGia,n.tenNXB,s.donGia,s.soCuon,d.soTap from SACH as s,NHAXUATBAN as n,DAUSACH as d,LOAISACH as l where ";
            connecstr += "n.maNXB = s.ma_NXB and s.maDS = d.maDauSach and l.maLoaiSach = s.ma_Loai and l.theLoai = '" + loaiSach + "'";
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public DataTable TimSachTheoTacGia(string tacGia)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select 1 as stt ,s.maSach,s.tenSach,d.tenDauSach, l.theLoai,s.tacGia,n.tenNXB,s.donGia,s.soCuon,d.soTap from SACH as s,NHAXUATBAN as n,DAUSACH as d,LOAISACH as l where ";
            connecstr += "n.maNXB = s.ma_NXB and s.maDS = d.maDauSach and l.maLoaiSach = s.ma_Loai and s.tacGia = '" + tacGia + "'";
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public DataTable TimSachTheoDauSach(string dauSach)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select 1 as stt,s.maSach,s.tenSach,d.tenDauSach, l.theLoai,s.tacGia,n.tenNXB,s.donGia,s.soCuon ,d.soTap from SACH as s,NHAXUATBAN as n,DAUSACH as d,LOAISACH as l where ";
            connecstr += "n.maNXB = s.ma_NXB and s.maDS = d.maDauSach and l.maLoaiSach = s.ma_Loai and d.tenDauSach = '" + dauSach + "'";
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public bool KiemTraSach(string maSach)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            DataTable d = new DataTable();
            string conecstr = "select * from SACH where maSach = '" + maSach + "'";
            OleDbDataAdapter adapter = new OleDbDataAdapter(conecstr,connect);
            adapter.Fill(d);
            connect.Close();
            if (d.Rows.Count > 0)
                return true;
            return false;
        }
        public void TangThemSoSach(string maSach,int Cuon)
        {
            int tong = soCuonSach(maSach);
            tong = tong + Cuon;
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "update SACH set soCuon = ?  where maSach = '" + maSach + "' ";
            OleDbCommand command = new OleDbCommand(connecstr,connect);
            command.Parameters.Add("soCuon", OleDbType.Integer);
            command.Parameters["soCuon"].Value = tong;
            command.ExecuteNonQuery();
            connect.Close();
        }
        public void GiamSoSach(string maSach)
        {
            int tong = soCuonSach(maSach);
            tong = tong - 1;
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "update SACH set soCuon = ?  where maSach = '" + maSach + "' ";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.Parameters.Add("soCuon", OleDbType.Integer);
            command.Parameters["soCuon"].Value = tong ;
            command.ExecuteNonQuery();
            connect.Close();
        }
        public DataTable LayThongTinThanhLySach()
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string conecstr = "select 0 as chon,s.maSach,s.tenSach from SACH s";
            DataTable d = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter(conecstr, connect);
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public void GiamSachSauKhiThanhLy(string maSach,int soCuon)
        {
            int tong = soCuonSach(maSach);
            tong = tong - soCuon;
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "update SACH set soCuon = ?  where maSach = '" + maSach + "' ";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.Parameters.Add("soCuon", OleDbType.Integer);
            command.Parameters["soCuon"].Value = tong;
            command.ExecuteNonQuery();
            connect.Close();
        }
        public string LayGiaTriCuaSach(string maSach)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select donGia FROM SACH  where maSach =  '" + maSach + "'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            OleDbDataReader Reader = command.ExecuteReader();
            string giaTri = string.Empty;
            while (Reader.Read())
            {
                giaTri = Reader.GetString(0);
            }
            connect.Close();
            return giaTri;   
        }
       
    }
}
